from db_conn import pd_conn_wfw
import pandas as pd


sql1 = '''
-- 合伙人下单
SELECT 
    t1.f_virtual_warehouse_id,t3.f_staff_name,
    t2.f_part_name,sum(t2.f_num) 下单数量,left(t1.f_create_time,10) 下单时间
FROM `t_part_order` t1 
left join t_part_order_item t2 on t1.f_id=t2.f_part_order_id and t2.f_is_deleted=0
left join t_virtual_warehouse_staff t3 on t3.f_virtual_warehouse_id=t1.f_virtual_warehouse_id 
    and t3.f_is_deleted=0 and t3.f_staff_role=2
where t1.f_create_time>='2022-08-19' and t1.f_is_deleted=0
group by t1.f_virtual_warehouse_id,t2.f_part_name,left(t1.f_create_time,10);
'''
df1 = pd_conn_wfw(sql1, 'ygl_stock')

sql2 = '''
-- 合伙人到货
select 
    t1.f_virtual_warehouse_id,t2.f_staff_name,
    t1.f_part_name,sum(t1.f_part_qty) 入库数量,
    left(t1.f_create_time,10) 入库时间
from t_virtual_warehouse_stock_record t1 
left join t_virtual_warehouse_staff t2 on t1.f_virtual_warehouse_id=t2.f_virtual_warehouse_id 
    and t2.f_is_deleted=0 and t2.f_staff_role=2
where t1.f_is_deleted=0
and t1.f_create_time>='2022-08-19' and t1.f_type=1
group by t1.f_virtual_warehouse_id,t1.f_part_name,left(t1.f_create_time,10)
'''
df2 = pd_conn_wfw(sql2, 'ygl_stock')

sql3 = '''
-- 配件使用
select 
    t1.f_virtual_warehouse_id,t2.f_staff_name 合伙人,
    t1.f_part_name,t1.f_part_qty 配件数量,t1.f_task_id,t1.f_factory_id, 
    t1.f_create_time 使用时间,t1.f_operator_name 执行人
from t_virtual_warehouse_stock_record t1 
left join t_virtual_warehouse_staff t2 on t1.f_virtual_warehouse_id=t2.f_virtual_warehouse_id 
    and t2.f_is_deleted=0 and t2.f_staff_role=2
where t1.f_is_deleted=0
and t1.f_create_time>='2022-08-19' and t1.f_type=2
'''
df3 = pd_conn_wfw(sql3, 'ygl_stock')

sql4 = '''
-- 配件更换、报废
select 
    t1.f_virtual_warehouse_id,t2.f_staff_name 合伙人,t1.f_part_name,
    sum(if(t1.f_type=1,t1.f_part_qty,0)) 报废数量, sum(if(t1.f_type=2,t1.f_part_qty,0)) 退回数量
from t_old_part_record t1 
left join t_virtual_warehouse_staff t2 on t1.f_virtual_warehouse_id=t2.f_virtual_warehouse_id 
    and t2.f_is_deleted=0 and t2.f_staff_role=2
where t1.f_is_deleted=0
group by t1.f_virtual_warehouse_id,t2.f_staff_name,t1.f_part_name
'''
df4 = pd_conn_wfw(sql4, 'ygl_stock')

sql5 = '''
-- 合伙人实时库存
SELECT 
    t1.f_virtual_warehouse_id,t2.f_staff_name 合伙人,
    t1.f_part_code,t1.f_part_name,t1.f_part_qty 库存数量,
    t1.f_update_time 最近更新时间
FROM `t_virtual_warehouse_stock` t1
left join t_virtual_warehouse_staff t2 on t1.f_virtual_warehouse_id=t2.f_virtual_warehouse_id 
    and t2.f_is_deleted=0 and t2.f_staff_role=2
where t1.f_is_deleted=0
'''
df5 = pd_conn_wfw(sql5, 'ygl_stock')


w = pd.ExcelWriter('output/合伙人配件.xlsx')
df1.to_excel(w, sheet_name='商城下单', index=False)
df2.to_excel(w, sheet_name='到货入库', index=False)
df3.to_excel(w, sheet_name='配件使用', index=False)
df4.to_excel(w, sheet_name='配件退回、报废', index=False)
df5.to_excel(w, sheet_name='合伙人实时库存', index=False)
w.save()
w.close()


